package com.jianglin.mapper;

import com.jianglin.model.RecordVo;
import com.jianglin.model.Records;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;

import java.util.List;
@Mapper
@Component("recordsDao")
public interface RecordsMapper{
    // 使用动态 SQL 注解实现复杂查询
    @Select("<script>" +
            "SELECT rec.*, hou.numbers, own.username, type.`name` as typename " +
            "FROM records rec " +
            "LEFT JOIN property_type type ON type.id = rec.type_id " +
            "LEFT JOIN house hou ON hou.id = rec.house_id " +
            "LEFT JOIN OWNER own ON own.house_id = hou.id " +
            "<where>" +
            "   <if test='numbers != null and numbers != \"\"'> " +
            "       AND numbers LIKE CONCAT('%', #{numbers}, '%') " +
            "   </if> " +
            "   <if test='typeId != null'> " +
            "       AND type_id = #{typeId} " +
            "   </if> " +
            "</where>" +
            "</script>")
    List<RecordVo> queryRecordsAll(RecordVo recordVo);

    // 使用 LIMIT 实现单条记录查询
    @Select("SELECT * FROM records " +
            "WHERE house_id = #{houId} AND type_id = #{typeId} " +
            "ORDER BY id DESC LIMIT 1")
    Records queryByHouIdAndTypeId(@Param("houId") Integer houId, @Param("typeId") Integer typeId);

    // 手动实现 CRUD 方法
    @Insert("INSERT INTO records (house_id, type_id, num, num2, on_time, up_time, check_time, remarks, meter) " + // 添加 meter
            "VALUES (#{houseId}, #{typeId}, #{num}, #{num2}, #{onTime}, #{upTime}, #{checkTime}, #{remarks}, #{meter})") // 添加 #{meter}
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Records records);

    @Delete("DELETE FROM records WHERE id = #{id}")
    int deleteById(Long id);

    @Update("UPDATE records SET " +
            "house_id = #{houseId}, type_id = #{typeId}, num = #{num}, " +
            "num2 = #{num2}, on_time = #{onTime}, up_time = #{upTime}, " +
            "check_time = #{checkTime}, remarks = #{remarks}, meter = #{meter} " + // 添加 meter
            "WHERE id = #{id}")
    int updateById(Records records);

    @Select("SELECT * FROM records WHERE id = #{id}")
    Records selectById(Long id);
}
